/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package orest.common.dao;


import java.sql.*;
import orest.common.response.*;
import orest.common.util.DataBase;
import orest.common.util.Tools;
import java.util.ArrayList;
import orest.common.valueobject.UnitVal;
/**
 *
 * @author admin
 */
public class UnitDao {

  public UnitDao() {
    }



    public boolean findUnitByPrk(Response rp, String unitid){
        boolean result = true;
        ArrayList al = new ArrayList();

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String sql = "select * from  T_Unit where  UnitID = ?";
        /* sql string end */

        /*
           get all of value of the production.
         */
        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            pstmt.setString(1, unitid);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                 al.add (new UnitVal(
                                     rs.getString ("UnitID"),
                                     rs.getString("Item_9"),
                                     rs.getString("Item_420"),
                                     rs.getString("Item_37"),
                                     rs.getString("Item_38"),
                                     rs.getString("Item_39"),
                                     rs.getString("Item_40"),
                                     rs.getString("Item_60"),
                                     rs.getString("Item_62"),                                     
                                     rs.getString("Item_41_3"),
                                     rs.getString("Item_63"),
                                     rs.getString("Item_64"),
                                     rs.getString("UnitPicture"),
                                     rs.getString("UnitIntro"),                                    
                                     rs.getString("NewEnergyClass1"),
                                     rs.getString("NewEnergyClass2"),
                                     rs.getString("TopicWords"),
                                     rs.getString("ChannelShow"),
                                     rs.getString("HomepageShow")
                                    ));
            }
            rp.setValObjs (al);
            return result;
        }
        catch(SQLException e){
            System.out.println ("When get unit info," +
                    " there is a exception occured!! ");
            System.out.println ("The class is UnitDao! " +
                                 "The function is findUnitByPrk! " +
                                 e);
            result = false;
            return result;
        }
        catch(Exception e){
            System.out.println ("When call the getConnection, have a"+
                                 "exception occured!   ");
            System.out.println ("The class is UnitDao and the " +
                                 "function is findUnitByPrk! " + e);
            result = false;
            return result;
        }
        finally{
            /* close the prestatement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of UnitDao " +
                            "and the function is findUnitByPrk, when" +
                            " close the pstmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of UnitDao and " +
                            "the function is findUnitByPrk, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of UnitDao and" +
                            "the function is findUnitByPrk, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }

        


   

     

         public long countUnitByCondition(String condtion, Response rp) {

        ArrayList al = new ArrayList();
        long total = 0;

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String sql = "select count(1) as cnt from  T_Unit where  " + condtion;
        /* sql string end */

        /*
           count  production then encapsulat as the entity bean
           and save them into ArrayList
         */
        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                total = rs.getLong("cnt");
            }
            return total;
        }
        catch(SQLException e){
            System.out.println("When countUnitByCondition, have a exception!" +
                                " The class name is UnitDao." + e);
            return 0;
        }
        catch(Exception e){
            System.out.println ("When call the countUnitByCondition, have a"+
                                 "exception occured!   ");
            e.printStackTrace();

            return 0;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of UnitDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of UnitDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of UnitDao and" +
                            " the function is countUnitByCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }

          public boolean findUnitByCondition(Response rp,String condtion,
                                                                int pageNo,
                                                                int pageRecord,
                                                                int total) {

        boolean result = true;
      
        ArrayList al = new ArrayList();
        int iPrePageCount ;
        int tempRow = 0;
        Tools tool = new Tools();

        /* number of record in page */
        int startRow = (pageNo -1) * pageRecord;
        int endRow = pageNo * pageRecord;

        if(endRow > total)
            endRow = total;

        tempRow = 1;

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String rowcount = "select top  " + Integer.toString(endRow);
        String sql = "UnitID,Item_9,UnitIntro,UnitPicture from  T_Unit where  " + condtion;

         sql += " order by UnitID desc";
        /* sql string end */

        /*
           count plan production then encapsulat as the entity bean
           and save them into ArrayList
         */
        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (rowcount + sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                if(tempRow > startRow) {
                 UnitVal UnitVal = new UnitVal();
                 UnitVal.setUnitID(rs.getString("UnitID"));
                 UnitVal.setItem_9(rs.getString("Item_9"));
                 UnitVal.setUnitIntro(rs.getString("UnitIntro"));
                 UnitVal.setUnitPicture(rs.getString("UnitPicture"));
                 al.add (UnitVal);
                }

                else
                    tempRow++;
            }

            
                rp.setValObjs (al);

            return true;
        }
        catch(SQLException e){
            System.out.println("When get the unit info, have a exception!" +
                                " The class name is UnitDao." + e);
            return false;
        }
        catch(Exception e){
            System.out.println ("When call the getConnection, have a"+
                                 "exception occured!   ");
            System.out.println ("The class is UnitDao and the " +
                                 "function is findUnitByCondition! " + e);

            return false;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                  
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of UnitDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of UnitDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of UnitDao and" +
                            " the function is findUnitByCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }

     public boolean findTopUnitByCondition(Response rp,String num,String con,String sorttype) {

        boolean result = true;
        ArrayList al = new ArrayList();
        Tools tool = new Tools();



        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String rowcount = "select top  " + num;
        String sql = " UnitID ,Item_9 from T_Unit where " + con ;
        
        if (sorttype.equals("r"))
              sql+=" order by ChannelShow desc,UnitID ";
        else
              sql+=" order by UnitID ";

        /* sql string end */


        try{

            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (rowcount + sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                 UnitVal UnitVal = new UnitVal();
                 UnitVal.setUnitID(rs.getString("UnitID"));
                 UnitVal.setItem_9(rs.getString("Item_9"));
                 al.add (UnitVal);
            }


                rp.setValObjs (al);

            return true;
        }
        catch(SQLException e){
            System.out.println("When get the unit info, have a exception!" +
                                " The class name is UnitDao." + e);
            return false;
        }
        catch(Exception e){
            System.out.println ("When call the getConnection, have a"+
                                 "exception occured!   ");
            System.out.println ("The class is UnitDao and the " +
                                 "function is findTopUnitByCondition! " + e);

            return false;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{

                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of UnitDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of UnitDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of UnitDao and" +
                            " the function is findTopUnitByCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }

       public boolean SetChannelShow_Batch(String ifshow,String[] id) {
        boolean flag = false;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();


        String sql = "update T_Unit set ChannelShow='"+ifshow +"' where UnitID=?";

       try {
            conn = db.getSysConnectionPool ();
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < id.length; i++) {
                pstmt.setString(1, id[i]);
                // ʹ������
                pstmt.addBatch();
             }
            // ִ������
           int[] result = pstmt.executeBatch();
           if (result[0] > 0) {
                flag = true;

            }
                return flag;
            }
            catch(SQLException e){
                e.printStackTrace();
                flag = false;
                return flag;
            }
            catch(Exception e){
                e.printStackTrace();
                flag = false;
                return flag;
            }
            finally {
                if(null != pstmt){
                    try{
                        pstmt.close ();
                        pstmt = null;
                    }
                    catch(SQLException e){
                        e.printStackTrace();
                     }
                    }

                     /* release the conn */
                     if(null != conn){
                     try{
                        conn.close();
                        conn = null;
                         db = null;
                        }
                        catch(Exception e){
                            e.printStackTrace();
                        }
                    }
                }


 }


        public boolean setChannelShow(String unitid,String ifshow){
    	boolean result = true;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();

        String sql = "";
        try{
                   sql = " update  T_Unit set ";
                   sql += "ChannelShow = '"+ ifshow +"' ";
                  sql += "where UnitID = '" + unitid + "'";
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            pstmt.execute();
            return result;
        }
        catch(SQLException e){
            e.printStackTrace();
            result = false;
            return result;
        }
        catch(Exception e){
            e.printStackTrace();
            result = false;
            return result;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                	e.printStackTrace();
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                	e.printStackTrace();
                }
            }
        }
    }

         public boolean findUnitShowByConditon(Response rp,String condtion,
                                                                int pageNo,
                                                                int pageRecord,
                                                                int total) {

        boolean result = true;
        ArrayList al = new ArrayList();
        int iPrePageCount ;
        int tempRow = 0;
        Tools tool = new Tools();

        /* number of record in page */
        int startRow = (pageNo -1) * pageRecord;
        int endRow = pageNo * pageRecord;

        if(endRow > total)
            endRow = total;




        tempRow = 1;

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String rowcount = "select top  " + Integer.toString(endRow);
        String sql = "  UnitID,Item_9,Item_64,NewEnergyClass1,NewEnergyClass2,ChannelShow,HomepageShow from  T_Unit where  " + condtion;



            sql += " order by UnitID ";
        /* sql string end */

        /*
           count plan production then encapsulat as the entity bean
           and save them into ArrayList
         */
        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (rowcount + sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                if(tempRow > startRow) {
                 UnitVal UnitVal = new UnitVal();
                 UnitVal.setUnitID(rs.getString("UnitID"));
                 UnitVal.setItem_9(rs.getString("Item_9"));
                 UnitVal.setItem_64(rs.getString("Item_64"));
                 UnitVal.setNewEnergyClass1(rs.getString("NewEnergyClass1"));
                 UnitVal.setNewEnergyClass2(rs.getString("NewEnergyClass2"));
                 UnitVal.setChannelShow(rs.getString("ChannelShow"));
                 UnitVal.setHomepageShow(rs.getString("HomepageShow"));
                 al.add (UnitVal);
                }

                else
                    tempRow++;
            }


                rp.setValObjs (al);

            return true;
        }
        catch(SQLException e){
            System.out.println("When find the unit info, have a exception!" +
                                " The class name is UnitDao." + e);
            return false;
        }
        catch(Exception e){
            System.out.println ("When call the getMyConnection, have a"+
                                 "exception occured!   ");
            System.out.println ("The class is UnitDao and the " +
                                 "function is findUnitShowByCondition! " + e);

            return false;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{

                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of UnitDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of UnitDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of UnitDao and" +
                            " the function is findUnitShowByCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }

}
